package com.kaikeba.dao.Impl;

import com.kaikeba.bean.BordUserInfo;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.util.DruidUtil;
import org.jacoco.agent.rt.internal_43f5073.core.internal.flow.IFrame;

import javax.management.DescriptorRead;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoMySql implements BaseUserDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) data_size, COUNT(TO_DAYS(REGTIME)=TO_DAYS(NOW()) OR NULL) data_day FROM EUSER";
    private static final String SQL_FIND_ALL = "SELECT * FROM EUSER";
    private static final String SQL_FIND_USER_PHONE = "SELECT USERPHONE FROM EUSER";
    private static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM EUSER LIMIT ?,?";
    private static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EUSER WHERE USERPHONE=?";
    private static final String SQL_ADD = "INSERT INTO EUSER(USERNAME,USERPHONE,NUMBER,PASSWORD,REGTIME,LASTLOGINTIME) VALUES(?,?,?,?,NOW(),NOW())";
    private static final String SQL_PUDATE = "UPDATE EUSER SET USERNAME=?,USERPHONE=?,NUMBER=?,PASSWORD=? WHERE ID=?";
    private static final String SQL_DELETE = "DELETE FROM EUSER WHERE USERPHONE=?";
    private static final String SQL_DELETE_BY_ID = "DELETE FROM EUSER WHERE ID=?";
    private static final String SQL_GET_RANK = "SELECT euser.username AS uname,COUNT(express.userphone) AS nums FROM euser LEFT JOIN express ON euser.username=express.username GROUP BY euser.username UNION SELECT c.courierName As uname,COUNT(e.userphone) AS nums FROM ecourier AS c LEFT JOIN express AS e ON c.courierName=e.username GROUP BY c.courierName ORDER BY nums DESC";
    private static final String SQL_GET_RANK_BY_MONTH = "SELECT euser.username AS uname,COUNT(express.userphone) AS nums FROM euser LEFT JOIN express ON euser.username=express.username AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= express.intime  GROUP BY euser.username UNION SELECT c.courierName As uname,COUNT(e.userphone) AS nums FROM ecourier AS c LEFT JOIN express AS e ON c.courierName=e.username AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= e.intime GROUP BY c.courierName ORDER BY nums DESC";
    private static final String SQL_GET_RANK_BY_YEAR = "SELECT euser.username AS uname,COUNT(express.userphone) AS nums FROM euser LEFT JOIN express ON euser.username=express.username AND YEAR(express.intime)=YEAR(NOW()) GROUP BY euser.username UNION SELECT c.courierName As uname,COUNT(e.userphone) AS nums FROM ecourier AS c LEFT JOIN express AS e ON c.courierName=e.username AND YEAR(e.intime)=YEAR(NOW()) GROUP BY c.courierName ORDER BY nums DESC";


    /**
     * 返回用户人数和日注册量
     * @return [{data_size:总数,data_day:新增}]
     */
    public List<Map<String,Integer>> console(){
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_CONSOLE);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            if(resultSet.next()){
                int data_size = resultSet.getInt("data_size");
                int data_day = resultSet.getInt("data_day");
                Map data1 = new HashMap();
                data1.put("data_size",data_size);
                data1.put("data_day",data_day);
                data.add(data1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有用户手机号
     *
     * @return
     */
    @Override
    public List<String> findUserPhone() {
        ArrayList<String> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_FIND_USER_PHONE);
            //添加参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //处理返回结果
            while (resultSet.next()){
                data.add(resultSet.getString("userphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 查询所有用户
     *
     * @return 所有用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            if (limit){
                //预编译SQL语句
                statement = conn.prepareStatement(SQL_FIND_ALL_LIMIT);
                //添加参数（可选）
                statement.setInt(1,offset);
                statement.setInt(2,pageNumber);
            }else{
                //预编译SQL语句
                statement = conn.prepareStatement(SQL_FIND_ALL);
                //添加参数（可选）
            }
            //执行SQL语句
            resultSet = statement.executeQuery();
            //处理返回结果
            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setUserPhone(resultSet.getString("userphone"));
                user.setPassword(resultSet.getString("password"));
                user.setNumber(resultSet.getString("number"));
                user.setRegTime(resultSet.getTimestamp("regtime"));
                user.setLastLoginTime(resultSet.getTimestamp("lastlogintime"));
                data.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 根据手机号查询用户信息
     *
     * @param userPhone 用户手机号
     * @return 返回单个用户的信息
     */
    @Override
    public User findByUserPhone(String userPhone) {
        User user = null;
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_FIND_BY_USERPHONE);
            //添加参数（可选）
            statement.setString(1,userPhone);
            //执行SQL语句
            resultSet = statement.executeQuery();
            //处理返回结果
            if (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setUserPhone(resultSet.getString("userphone"));
                user.setPassword(resultSet.getString("password"));
                user.setNumber(resultSet.getString("number"));
                user.setRegTime(resultSet.getTimestamp("regtime"));
                user.setLastLoginTime(resultSet.getTimestamp("lastlogintime"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return user;
    }

    /**
     * 增加新用户
     *
     * @param user 用户
     * @return 添加成功返回true, 失败返回false
     */
    @Override
    public boolean add(User user) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_ADD);
            //添加参数（可选）
            statement.setString(1,user.getUsername());
            statement.setString(2,user.getUserPhone());
            statement.setString(3,user.getNumber());
            statement.setString(4,user.getPassword());
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 增加新用户
     *
     * @param user 用户
     * @return 添加成功返回true, 失败返回false
     */
    @Override
    public boolean update(User user) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_PUDATE);
            //添加参数（可选）
            statement.setString(1,user.getUsername());
            statement.setString(2,user.getUserPhone());
            statement.setString(3,user.getNumber());
            statement.setString(4,user.getPassword());
            statement.setInt(5,user.getId());
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 根据手机号删除用户信息
     *
     * @param userPhone 用户手机号
     * @return
     */
    @Override
    public boolean delete(String userPhone) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_DELETE);
            //添加参数（可选）
            statement.setString(1,userPhone);
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 根据手机号删除用户信息
     *
     * @param id 用户id
     * @return
     */
    @Override
    public boolean deleteById(int id) {
        //获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            //预编译SQL语句
            statement = conn.prepareStatement(SQL_DELETE_BY_ID);
            //添加参数（可选）
            statement.setInt(1,id);
            //执行SQL语句
            int i = statement.executeUpdate();
            //处理返回结果
            return i>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            DruidUtil.close(conn,statement);
        }
        return false;
    }

    /**
     * 得到总排名
     *
     * @return
     */
    @Override
    public List<BordUserInfo> getRank() {
        ArrayList<BordUserInfo> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_GET_RANK);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            while (resultSet.next()){
                BordUserInfo bordUserInfo = new BordUserInfo();
                bordUserInfo.setName(resultSet.getString("uname"));
                bordUserInfo.setNum(resultSet.getInt("nums"));
                data.add(bordUserInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 得到年排名
     *
     * @return
     */
    @Override
    public List<BordUserInfo> getRankByYear() {
        ArrayList<BordUserInfo> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_GET_RANK_BY_MONTH);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            while (resultSet.next()){
                BordUserInfo bordUserInfo = new BordUserInfo();
                bordUserInfo.setName(resultSet.getString("uname"));
                bordUserInfo.setNum(resultSet.getInt("nums"));
                data.add(bordUserInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }

    /**
     * 得到月排名
     *
     * @return
     */
    @Override
    public List<BordUserInfo> getRankByMonth() {
        ArrayList<BordUserInfo> data = new ArrayList<>();
        //获取数据库连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        //预编译SQL语句
        try {
            statement = conn.prepareStatement(SQL_GET_RANK_BY_YEAR);
            //填充参数（可选）
            //执行SQL语句
            resultSet = statement.executeQuery();
            //获取结果
            while (resultSet.next()){
                BordUserInfo bordUserInfo = new BordUserInfo();
                bordUserInfo.setName(resultSet.getString("uname"));
                bordUserInfo.setNum(resultSet.getInt("nums"));
                data.add(bordUserInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(conn,statement,resultSet);
        }
        return data;
    }
}
